/*
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License.
 * The Original Code is Openbravo ERP.
 * The Initial Developer of the Original Code is Openbravo SLU
 * All portions are Copyright (C) 2010-2012 Openbravo SLU
 * All Rights Reserved.
 * Contributor(s):  ______________________________________.
 ************************************************************************
 */
package org.openbravo.erpCommon.businessUtility;

import java.io.File;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.openbravo.base.exception.OBException;
import org.openbravo.base.provider.OBProvider;
import org.openbravo.base.session.OBPropertiesProvider;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBCriteria;
import org.openbravo.dal.service.OBDal;
import org.openbravo.dal.service.OBQuery;
import org.openbravo.erpCommon.utility.DimensionDisplayUtility;
import org.openbravo.erpCommon.utility.Utility;
import org.openbravo.model.ad.access.Role;
import org.openbravo.model.ad.access.RoleOrganization;
import org.openbravo.model.ad.access.User;
import org.openbravo.model.ad.access.UserRoles;
import org.openbravo.model.ad.datamodel.Table;
import org.openbravo.model.ad.module.ADClientModule;
import org.openbravo.model.ad.module.ADOrgModule;
import org.openbravo.model.ad.module.Module;
import org.openbravo.model.ad.system.Client;
import org.openbravo.model.ad.system.ClientInformation;
import org.openbravo.model.ad.system.DimensionMapping;
import org.openbravo.model.ad.system.Language;
import org.openbravo.model.ad.system.SystemInformation;
import org.openbravo.model.ad.ui.ElementTrl;
import org.openbravo.model.ad.ui.Message;
import org.openbravo.model.ad.ui.MessageTrl;
import org.openbravo.model.ad.utility.DataSet;
import org.openbravo.model.ad.utility.Image;
import org.openbravo.model.ad.utility.Sequence;
import org.openbravo.model.ad.utility.TableTree;
import org.openbravo.model.ad.utility.Tree;
import org.openbravo.model.ad.utility.TreeNode;
import org.openbravo.model.common.currency.Currency;
import org.openbravo.model.common.enterprise.DocumentType;
import org.openbravo.model.common.enterprise.Organization;
import org.openbravo.model.common.enterprise.OrganizationType;
import org.openbravo.service.db.DataImportService;
import org.openbravo.service.db.ImportResult;

/**
 * @author David Alsasua
 * Initial Client Setup Utility class
 */
public class InitialSetupUtility {
  private static final Logger log4j = Logger.getLogger(InitialSetupUtility.class);

  /**
   * 
   * @param strClient
   *          name of the client
   * @return true if exists client in database with provided name
   * @throws Exception
   */
  public static boolean existsClientName(String strClient) throws Exception {
    final OBCriteria<Client> obcClient = OBDal.getInstance().createCriteria(Client.class);
    obcClient.add(Restrictions.eq(Client.PROPERTY_NAME, strClient));
    return obcClient.count() > 0;
  }

  /**
   * 
   * @param strUser
   *          user name
   * @return true if exists a user with the name provided in database
   * @throws Exception
   */
  public static boolean existsUserName(String strUser) throws Exception {
    OBContext.setAdminMode();
    try {
      final OBCriteria<User> obcUser = OBDal.getInstance().createCriteria(User.class);
      obcUser.setFilterOnReadableClients(false);
      obcUser.setFilterOnReadableOrganization(false);
      obcUser.add(Restrictions.eq(User.PROPERTY_USERNAME, strUser));
      return obcUser.count() > 0;
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * 
   * @param strClientName
   *          client name
   * @param strCurrency
   *          currency id
   * @return Client object for the created client
   * @throws Exception
   */
  public static Client insertClient(String strClientName, String strCurrency) throws Exception {
    log4j.debug("InitialSetupUtility - insertClient() - clientName = " + strClientName);
    Currency currency = getCurrency(strCurrency);
    final Client newClient = OBProvider.getInstance().get(Client.class);
    newClient.setCurrency(currency);
    newClient.setSearchKey(strClientName);
    newClient.setName(strClientName);
    newClient.setDescription(strClientName);
    newClient.setNewOBObject(true);

    try {
      // Accounting dimension display enabled
      newClient.setAcctdimCentrallyMaintained(true);
      Map<String, String> readOnlySessionVariableMap = DimensionDisplayUtility
          .getReadOnlyLogicSessionVariables();

      // Organization
      newClient.setOrgAcctdimHeader("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_Organization + "_" + DimensionDisplayUtility.DIM_Header)));
      newClient.setOrgAcctdimLines("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_Organization + "_" + DimensionDisplayUtility.DIM_Lines)));
      newClient.setOrgAcctdimBreakdown("Y".equals(readOnlySessionVariableMap.get("$RO_"
              + DimensionDisplayUtility.DIM_Organization + "_"
              + DimensionDisplayUtility.DIM_BreakDown)));
      newClient.setOrgAcctdimIsenable(newClient.isOrgAcctdimHeader()
          || newClient.isOrgAcctdimLines() || newClient.isOrgAcctdimBreakdown());

      // Product
      newClient.setProductAcctdimHeader("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_Product + "_" + DimensionDisplayUtility.DIM_Header)));
      newClient.setProductAcctdimLines("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_Product + "_" + DimensionDisplayUtility.DIM_Lines)));
      newClient.setProductAcctdimBreakdown("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_Product + "_" + DimensionDisplayUtility.DIM_BreakDown)));
      newClient.setProductAcctdimIsenable(newClient.isProductAcctdimHeader()
          || newClient.isProductAcctdimLines() || newClient.isProductAcctdimBreakdown());

      // User 1
      newClient.setUser1AcctdimHeader("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_User1 + "_" + DimensionDisplayUtility.DIM_Header)));
      newClient.setUser1AcctdimLines("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_User1 + "_" + DimensionDisplayUtility.DIM_Lines)));
      newClient.setUser1AcctdimBreakdown("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_User1 + "_" + DimensionDisplayUtility.DIM_BreakDown)));
      newClient.setUser1AcctdimIsenable(newClient.isUser1AcctdimHeader()
          || newClient.isUser1AcctdimLines() || newClient.isUser1AcctdimBreakdown());

      // User 2
      newClient.setUser2AcctdimHeader("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_User2 + "_" + DimensionDisplayUtility.DIM_Header)));
      newClient.setUser2AcctdimLines("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_User2 + "_" + DimensionDisplayUtility.DIM_Lines)));
      newClient.setUser2AcctdimBreakdown("Y".equals(readOnlySessionVariableMap.get("$RO_"
          + DimensionDisplayUtility.DIM_User2 + "_" + DimensionDisplayUtility.DIM_BreakDown)));
      newClient.setUser2AcctdimIsenable(newClient.isUser2AcctdimHeader()
          || newClient.isUser2AcctdimLines() || newClient.isUser2AcctdimBreakdown());

      OBDal.getInstance().save(newClient);
      OBDal.getInstance().flush();

      // Client Accounting Dimension configuration
      List<String> mandatoryDimensionList = new ArrayList<String>();
      mandatoryDimensionList.add(DimensionDisplayUtility.DIM_Organization);
      mandatoryDimensionList.add(DimensionDisplayUtility.DIM_Product);
      for (Object[] o : DimensionDisplayUtility.getGroupDimensionMapping(mandatoryDimensionList)) {
        String dimension = (String) o[0];
        String docBasetType = (String) o[1];
        boolean showInHeader = false;
        boolean showInLines = false;
        boolean showInBreakDown = false;
        for (DimensionMapping dm : DimensionDisplayUtility.getDimensionMappingList(dimension,
            docBasetType, null)) {
          if (!showInHeader) {
            showInHeader = dm.isMandatory()
                && DimensionDisplayUtility.DIM_Header.equals(dm.getLevel());
          }
          if (!showInLines) {
            showInLines = dm.isMandatory()
                && DimensionDisplayUtility.DIM_Lines.equals(dm.getLevel());
          }
          if (!showInBreakDown) {
            showInBreakDown = dm.isMandatory()
                && DimensionDisplayUtility.DIM_BreakDown.equals(dm.getLevel());
          }
        }
        DimensionDisplayUtility.createNewDimensionMapping(newClient, getZeroOrg(), dimension,
            docBasetType, showInHeader, showInLines, showInBreakDown);
      }
    } catch (Exception e) {
      log4j.error("Error creating client accounting dimension configuration", e);
    }

    OBDal.getInstance().flush();
    return newClient;
  }

  /**
   * 
   * @param strCurrencyID
   *          c_currency_id
   * @return Currency object that belongs to provided id
   * @throws Exception
   */
  public static Currency getCurrency(String strCurrencyID) throws Exception {
    return OBDal.getInstance().get(Currency.class, strCurrencyID);
  }

  /**
   * 
   * @param strLanguage
   *          language key (for example en_US)
   * @return Language object corresponding to provided key
   * @throws Exception
   */
  public static Language getLanguage(String strLanguage) throws Exception {
    final OBCriteria<Language> obcLanguage = OBDal.getInstance().createCriteria(Language.class);
    obcLanguage.add(Restrictions.eq(Language.PROPERTY_LANGUAGE, strLanguage));
    if (obcLanguage.list().size() > 0)
      return obcLanguage.list().get(0);
    else
      return null;
  }

  /**
   * @deprecated use tableTreeRelation, because it retrieves all tableTrees, instead of only the
   *             trees defined in the DA_TreeType type list reference Returns the relation of trees
   *             defined in the reference list of the application dictionary called AD_TreeType Type
   * 
   * @return java.util.List<org.openbravo.model.ad.domain.List>: the relation of AD list elements
   * @throws Exception
   */
  @Deprecated
  public static List<org.openbravo.model.ad.domain.List> treeRelation() throws Exception {

    final OBCriteria<org.openbravo.model.ad.domain.Reference> obcReference = OBDal.getInstance()
        .createCriteria(org.openbravo.model.ad.domain.Reference.class);
    obcReference.add(Restrictions.eq(org.openbravo.model.ad.domain.Reference.PROPERTY_NAME,
        "AD_TreeType Type"));
    List<org.openbravo.model.ad.domain.Reference> listReferences = obcReference.list();
    if (listReferences.size() != 1)
      return null;

    org.openbravo.model.ad.domain.Reference referenceTree = listReferences.get(0);
    final OBCriteria<org.openbravo.model.ad.domain.List> obcRefTreeList = OBDal.getInstance()
        .createCriteria(org.openbravo.model.ad.domain.List.class);
    obcRefTreeList.add(Restrictions.eq(org.openbravo.model.ad.domain.List.PROPERTY_REFERENCE,
        referenceTree));
    obcRefTreeList.addOrder(Order.asc("name"));
    return obcRefTreeList.list();
  }

  /**
   * Returns the relation of trees that use the ADTree tree structure, AD_TABLE_TREE table
   * 
   * @return java.util.List<TableTree>: the relation of all the trees that use the ADTree tree
   *         structure
   * @throws Exception
   */
  public static List<TableTree> tableTreeRelation() throws Exception {
    final OBCriteria<TableTree> obcTableTree = OBDal.getInstance().createCriteria(TableTree.class);
    obcTableTree.add(Restrictions.eq(TableTree.PROPERTY_TREESTRUCTURE, "ADTree"));
    obcTableTree.addOrder(Order.asc(TableTree.PROPERTY_NAME));
    return obcTableTree.list();
  }

  /**
   * Returns the tree of the provided type
   * 
   * @param strTreeTypeMenu
   *          two letters corresponding to the tree type for the menu
   * @return Tree menu element (defined at system level)
   * @throws Exception
   */
  public static Tree getSystemMenuTree(String strTreeTypeMenu) throws Exception {
    final String AD_MENU_ID = "116";
    Table menuTable = OBDal.getInstance().get(Table.class, AD_MENU_ID);
    final OBCriteria<Tree> obcTree = OBDal.getInstance().createCriteria(Tree.class);
    obcTree.add(Restrictions.eq(Tree.PROPERTY_TABLE, menuTable));
    List<Tree> lTrees = obcTree.list();
    if (lTrees.size() != 1)
      return null;
    return lTrees.get(0);
  }

  /**
   * @deprecated use new insertTree method where new parameter "table" is added
   * @param client
   * @param name
   * @param treeType
   * @param boIsAllNodes
   * @return object Tree for the new tree
   * @throws Exception
   */
  @Deprecated
  public static Tree insertTree(Client client, String name, String treeType, Boolean boIsAllNodes)
      throws Exception {
    return insertTree(client, name, treeType, boIsAllNodes, null);
  }

  public static Tree insertTree(Client client, String name, String treeType, Boolean boIsAllNodes,
      Table table) throws Exception {
    final Tree newTree = OBProvider.getInstance().get(Tree.class);
    newTree.setClient(client);
    newTree.setName(name);
    newTree.setDescription(name);
    newTree.setTypeArea(treeType);
    newTree.setAllNodes(boIsAllNodes);
    newTree.setTable(table);
    OBDal.getInstance().save(newTree);
    OBDal.getInstance().flush();
    return newTree;
  }

  /**
   * 
   * @param client
   * @param menuTree
   * @param orgTree
   * @param bpartnerTree
   * @param projectTree
   * @param salesRegionTree
   * @param productTree
   * @param campaignTree
   * @param boDiscountCalculatedFromLineAmounts
   * @return ClientInformation object for the new element
   * @throws Exception
   */
  public static ClientInformation insertClientinfo(Client client, Tree menuTree, Tree orgTree,
      Tree productTree, Tree campaignTree, Boolean boDiscountCalculatedFromLineAmounts) throws Exception {
    final ClientInformation newClientInfo = OBProvider.getInstance().get(ClientInformation.class);
    newClientInfo.setClient(client);
    newClientInfo.setPrimaryTreeMenu(menuTree);
    newClientInfo.setPrimaryTreeOrganization(orgTree);
    newClientInfo.setPrimaryTreeProduct(productTree);
    if (campaignTree != null)
      newClientInfo.setTreeCampaign(campaignTree);
    newClientInfo.setDiscountCalculatedFromLineAmounts(boDiscountCalculatedFromLineAmounts);
    OBDal.getInstance().save(newClientInfo);
    OBDal.getInstance().flush();
    return newClientInfo;
  }

  /**
   * Associates a client info record to a client
   * 
   * @param client
   * @param clientInfo
   * @return true if update was correct
   * @throws Exception
   */
  public static boolean setClientInformation(Client client, ClientInformation clientInfo)
      throws Exception {
    boolean boResult = client.getClientInformationList().add(clientInfo);
    OBDal.getInstance().save(client);
    OBDal.getInstance().flush();
    return boResult;
  }

  /**
   * 
   * @param client
   * @throws Exception
   */
  public static void setOrgImage(Client client, Organization org, byte[] image, String strImageName)
      throws Exception {
    Image yourCompanyDocumentImage = OBProvider.getInstance().get(Image.class);
    yourCompanyDocumentImage.setClient(client);
    yourCompanyDocumentImage.setOrganization(org);
    yourCompanyDocumentImage.setBindaryData(image);
    yourCompanyDocumentImage.setName(strImageName);
    org.getOrganizationInformationList().get(0)
        .setYourCompanyDocumentImage(yourCompanyDocumentImage);
    yourCompanyDocumentImage.setOrganization(org);
    OBDal.getInstance().save(yourCompanyDocumentImage);
    OBDal.getInstance().save(org);
    OBDal.getInstance().flush();
  }

  /**
   * 
   * @param client
   * @throws Exception
   */
  public static void setClientImages(Client client) throws Exception {
    SystemInformation sys = OBDal.getInstance().get(SystemInformation.class, "0");
    setYourCompanyBigImage(sys, client);
    setYourCompanyDocumentImage(sys, client);
    setYourCompanyMenuImage(sys, client);
  }

  /**
   * 
   * @param sys
   * @param client
   */
  public static void setYourCompanyBigImage(SystemInformation sys, Client client) {
    Image yourCompanyBigImage = OBProvider.getInstance().get(Image.class);
    Image systemCompanyBigImage = sys.getYourCompanyBigImage();
    if (systemCompanyBigImage != null) {
      yourCompanyBigImage.setClient(client);
      yourCompanyBigImage.setBindaryData(systemCompanyBigImage.getBindaryData());
      yourCompanyBigImage.setName(systemCompanyBigImage.getName());
      client.getClientInformationList().get(0).setYourCompanyBigImage(yourCompanyBigImage);
      OBDal.getInstance().save(yourCompanyBigImage);
      OBDal.getInstance().flush();
    }
  }

  /**
   * 
   * @param sys
   * @param client
   */
  public static void setYourCompanyDocumentImage(SystemInformation sys, Client client) {
    Image yourCompanyDocumentImage = OBProvider.getInstance().get(Image.class);
    if (sys.getYourCompanyDocumentImage() != null) {
      yourCompanyDocumentImage.setClient(client);
      yourCompanyDocumentImage.setBindaryData(sys.getYourCompanyDocumentImage().getBindaryData());
      yourCompanyDocumentImage.setName(sys.getYourCompanyBigImage().getName());
      client.getClientInformationList().get(0)
          .setYourCompanyDocumentImage(yourCompanyDocumentImage);
      OBDal.getInstance().save(yourCompanyDocumentImage);
      OBDal.getInstance().flush();
    }
  }

  /**
   * 
   * @param sys
   * @param client
   */
  public static void setYourCompanyMenuImage(SystemInformation sys, Client client) {
    Image yourCompanyMenuImage = OBProvider.getInstance().get(Image.class);
    if (sys.getYourCompanyMenuImage() != null) {
      yourCompanyMenuImage.setClient(client);
      yourCompanyMenuImage.setBindaryData(sys.getYourCompanyMenuImage().getBindaryData());
      yourCompanyMenuImage.setName(sys.getYourCompanyMenuImage().getName());
      client.getClientInformationList().get(0).setYourCompanyMenuImage(yourCompanyMenuImage);
      OBDal.getInstance().save(yourCompanyMenuImage);
      OBDal.getInstance().flush();
    }
  }

  /**
   * 
   * @param client
   *          client for which the role will be created
   * @param orgProvided
   *          if null, role inserted for organization with id=0
   * @param name
   *          name of the role
   * @param strUserLevelProvided
   *          if null, user level " CO" will be set to the new role
   * @return Role object for new element
   */
  public static Role insertRole(Client client, Organization orgProvided, String name,
      String strUserLevelProvided) throws Exception {
    return insertRole(client, orgProvided, name, strUserLevelProvided, true);
  }

  /**
   * 
   * @param client
   *          client for which the role will be created
   * @param orgProvided
   *          if null, role inserted for organization with id=0
   * @param name
   *          name of the role
   * @param strUserLevelProvided
   *          if null, user level " CO" will be set to the new role
   * @return Role object for new element
   */
  public static Role insertRole(Client client, Organization orgProvided, String name,
      String strUserLevelProvided, boolean isClientAdmin) throws Exception {
    Organization organization = null;
    if (orgProvided == null) {
      if ((organization = getZeroOrg()) == null)
        return null;
    } else
      organization = orgProvided;
    String strUserLevel;
    if (strUserLevelProvided == null || strUserLevelProvided.equals(""))
      strUserLevel = " CO";
    else
      strUserLevel = strUserLevelProvided;

    final Role newRole = OBProvider.getInstance().get(Role.class);
    newRole.setClient(client);
    newRole.setOrganization(organization);
    newRole.setName(name);
    newRole.setDescription(name);
    newRole.setClientList(client.getId());
    newRole.setOrganizationList(organization.getId());
    newRole.setUserLevel(strUserLevel);
    newRole.setClientAdmin(isClientAdmin);
    OBDal.getInstance().save(newRole);
    OBDal.getInstance().flush();
    return newRole;
  }

  /**
   * 
   * @param role
   *          role for which the organization access information will be created
   * @param orgProvided
   *          if null, organization with id "0" will be used
   * @return RoleOrganization object for new element
   */
  public static RoleOrganization insertRoleOrganization(Role role, Organization orgProvided)
      throws Exception {
    return insertRoleOrganization(role, orgProvided, false);
  }

  /**
   * 
   * @param role
   *          role for which the organization access information will be created
   * @param orgProvided
   *          if null, organization with id "0" will be used
   * @return RoleOrganization object for new element
   */
  public static RoleOrganization insertRoleOrganization(Role role, Organization orgProvided,
      boolean isOrgAdmin) throws Exception {
    OBContext.setAdminMode();
    try {
      Organization organization = null;
      if (orgProvided == null) {
        if ((organization = getZeroOrg()) == null)
          return null;
      } else
        organization = orgProvided;

      final RoleOrganization newRoleOrganization = OBProvider.getInstance().get(
          RoleOrganization.class);
      newRoleOrganization.setClient(role.getClient());
      newRoleOrganization.setOrganization(organization);
      newRoleOrganization.setRole(role);
      newRoleOrganization.setOrgAdmin(isOrgAdmin);
      OBDal.getInstance().save(newRoleOrganization);
      OBDal.getInstance().flush();
      return newRoleOrganization;
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * 
   * @param client
   * @param orgProvided
   * @param name
   * @param password
   * @param role
   * @param defaultLanguage
   * @return User object
   * @throws Exception
   */
  public static User insertUser(Client client, Organization orgProvided, String name,
      String password, Role role, Language defaultLanguage) throws Exception {
    Organization organization = null;
    if (orgProvided == null) {
      if ((organization = getZeroOrg()) == null)
        return null;
    } else
      organization = orgProvided;

    final User newUser = OBProvider.getInstance().get(User.class);
    newUser.setClient(client);
    newUser.setOrganization(organization);
    newUser.setName(name);
    newUser.setDescription(name);
    newUser.setUsername(name);
    newUser.setPassword(password);
    newUser.setDefaultLanguage(defaultLanguage);
    if (role != null)
      newUser.setDefaultRole(role);
    OBDal.getInstance().save(newUser);
    OBContext.setAdminMode(true);
    try {
      OBDal.getInstance().flush();
    } finally {
      OBContext.restorePreviousMode();
    }
    return newUser;
  }

  /**
   * 
   * @param client
   * @param user
   * @param orgProvided
   * @param role
   * @return UserRoles object for new element
   * @throws Exception
   */
  public static UserRoles insertUserRole(Client client, User user, Organization orgProvided,
      Role role) throws Exception {
    return insertUserRole(client, user, orgProvided, role, true);
  }

  /**
   * 
   * @param client
   * @param user
   * @param orgProvided
   * @param role
   * @return UserRoles object for new element
   * @throws Exception
   */
  public static UserRoles insertUserRole(Client client, User user, Organization orgProvided,
      Role role, boolean isRoleAdmin) throws Exception {
    OBContext.setAdminMode();
    try {
      Organization organization = null;

      if (orgProvided == null) {
        if ((organization = getZeroOrg()) == null)
          return null;
      } else
        organization = orgProvided;

      final UserRoles newUserRole = OBProvider.getInstance().get(UserRoles.class);
      newUserRole.setClient(client);
      newUserRole.setOrganization(organization);
      newUserRole.setRole(role);
      newUserRole.setUserContact(user);
      newUserRole.setRoleAdmin(isRoleAdmin);
      OBDal.getInstance().save(newUserRole);
      OBDal.getInstance().flush();
      return newUserRole;
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * Inserts a new role for the created client and user. Also user Openbravo will have rights to
   * access new client
   * 
   * @param client
   * @param user
   * @param organization
   * @param role
   * @throws Exception
   */
  public static void insertUserRoles(Client client, User user, Organization organization, Role role)
      throws Exception {
    insertUserRole(client, user, organization, role);
    insertUserRole(client, OBDal.getInstance().get(User.class, "100"), organization, role);
  }
  /**
   * 
   * @return Organization object for * organization (with id 0)
   */
  private static Organization getZeroOrg() {
    return OBDal.getInstance().get(Organization.class, "0");
  }
  /**
   * Returns the nodes of a given tree
   * 
   * @param accountTree
   * @param client
   * @param orgProvided
   * @return List<TreeNode> with relation of tree node elements of the provided tree
   * @throws Exception
   */
  public static List<TreeNode> getTreeNode(Tree accountTree, Client client, Organization orgProvided)
      throws Exception {
    Organization organization;
    if (orgProvided == null) {
      if ((organization = getZeroOrg()) == null)
        return null;
    } else
      organization = orgProvided;
    List<TreeNode> lTreeNodes;
    OBContext.setAdminMode();
    try {
      final OBCriteria<TreeNode> obcTreeNode = OBDal.getInstance().createCriteria(TreeNode.class);
      obcTreeNode.add(Restrictions.eq(TreeNode.PROPERTY_TREE, accountTree));
      obcTreeNode.add(Restrictions.eq(TreeNode.PROPERTY_CLIENT, client));
      obcTreeNode.add(Restrictions.eq(TreeNode.PROPERTY_ORGANIZATION, organization));
      if (OBContext.getOBContext().isInAdministratorMode()) {
        obcTreeNode.setFilterOnReadableClients(false);
        obcTreeNode.setFilterOnReadableOrganization(false);
      }
      lTreeNodes = obcTreeNode.list();
      return lTreeNodes;
    } catch (Exception e) {
      return null;
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * Returns the nodes of a given tree
   * 
   * @param accountTree
   * @param client
   * @return List<TreeNode> with relation of tree node elements of the provided tree
   * @throws Exception
   */
  public static List<TreeNode> getTreeNode(Tree accountTree, Client client) throws Exception {
    return getTreeNode(accountTree, client, null);
  }

  /**
   * Sorts the account tree (stored in ADTreeNode) according to the order provided
   * 
   * @param treeNodes
   *          relation of nodes in ADTreeNode belonging to the accounting tree to sort out
   * @param mapSequence
   *          HashMap<String,Long> where the String belongs to the value of a c_elementvalue, and
   *          Long to the sequence that must be assigned to the node that represents that element
   *          value in ADTreeNode
   * @param mapElementValueValue
   *          each tree node in treeNodes has one entry in mapElementValueId to link it's value with
   *          the c_elementvalue_id of that element in c_elementvalue table
   * @param mapElementValueId
   *          stores the link value <-> c_elementvalue_id
   * @param mapParent
   *          stores the link value <-> value of the parent
   * @param doFlush
   *          if true, each new update performs a flush in DAL
   * @throws Exception
   */
  public static void updateAccountTree(List<TreeNode> treeNodes, HashMap<String, Long> mapSequence,
      HashMap<String, String> mapElementValueValue, HashMap<String, String> mapElementValueId,
      HashMap<String, String> mapParent, boolean doFlush) throws Exception {
    OBContext.setAdminMode();
    try {
      Iterator<TreeNode> iTreeNodes = treeNodes.listIterator();
      while (iTreeNodes.hasNext()) {
        try {
          TreeNode treeNode = iTreeNodes.next();
          String strElementId = treeNode.getNode();
          String strElementValue = "0";
          Long lSequence = 10L;
          if (!strElementId.equals("0")) {
            strElementValue = mapElementValueValue.get(strElementId);
            lSequence = mapSequence.get(strElementValue);
            treeNode.setSequenceNumber(lSequence);
            String strParentValue = mapParent.get(strElementValue);
            if (!strParentValue.equals("0"))
              treeNode.setReportSet(mapElementValueId.get(strParentValue));
            OBDal.getInstance().save(treeNode);
          }
          if (doFlush)
            OBDal.getInstance().flush();
        } catch (Exception ignoredException) {
          log4j.error("updateAccountTree() - Ignored exception while sorting account tree.",
              ignoredException);
        }
      }
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * 
   * @param client
   * @param organization
   * @param name
   * @param startNo
   * @return Sequence object for the new created element
   */
  public static Sequence insertSequence(Client client, Organization organization, String name,
      Long startNo) {
    final Sequence newSequence = OBProvider.getInstance().get(Sequence.class);
    newSequence.setClient(client);
    newSequence.setOrganization(organization);
    newSequence.setName(name);
    newSequence.setStartingNo(startNo);
    OBDal.getInstance().save(newSequence);
    OBDal.getInstance().flush();
    return newSequence;
  }

  /**
   * 
   * @param client
   * @param organization
   * @param name
   * @param printName
   * @param docBaseType
   * @param docSubTypeSO
   * @param shipment
   * @param invoice
   * @param isDocNoControlled
   * @param sequence
   * @param category
   * @param isSOTrx
   * @param table
   * @return DocumentType object for the new element
   */
  public static DocumentType insertDocType(Client client, Organization organization, String name,
      String printName, String docBaseType, String docSubTypeSO, DocumentType shipment,
      DocumentType invoice, boolean isDocNoControlled, Sequence sequence, 
      boolean isSOTrx, Table table) {
    final DocumentType newDocumentType = OBProvider.getInstance().get(DocumentType.class);
    newDocumentType.setClient(client);
    newDocumentType.setOrganization(organization);
    newDocumentType.setName(name);
    newDocumentType.setPrintText(printName);
    newDocumentType.setDocumentCategory(docBaseType);
    newDocumentType.setSequencedDocument(isDocNoControlled);
    newDocumentType.setDocumentSequence(sequence);
    newDocumentType.setSalesTransaction(isSOTrx);
    newDocumentType.setTable(table);
    OBDal.getInstance().save(newDocumentType);
    OBDal.getInstance().flush();
    return newDocumentType;
  }

  /**
   * Given a dataset, inserts the elements in the xml file into database.
   * 
   * @param dataset
   * @param client
   * @param orgProvided
   * @return ImportResult object for the created element. Errors, warnings and log is provided in
   *         this object.
   * @throws Exception
   */
  public static ImportResult insertReferenceData(DataSet dataset, Client client,
      Organization orgProvided) throws Exception {
    Organization organization = null;
    if (orgProvided == null) {
      if ((organization = getZeroOrg()) == null)
        return null;
    } else
      organization = orgProvided;
    ImportResult myResult = null;
    String strSourcePath = OBPropertiesProvider.getInstance().getOpenbravoProperties()
        .getProperty("source.path");
    if (strSourcePath == null || strSourcePath.equals("")) {
      throw new OBException("@NoSourcePath@");
    }
    String strPath = "";
    File datasetFile;
    OBContext.setAdminMode();
    try {
      if (dataset.getModule().getJavaPackage().equals("org.openbravo")) {
        strPath = strSourcePath + "/referencedata/standard";
      } else {
        strPath = strSourcePath + "/modules/" + dataset.getModule().getJavaPackage()
            + "/referencedata/standard";
      }
      datasetFile = new File(strPath + "/" + Utility.wikifiedName(dataset.getName()) + ".xml");
      if (!datasetFile.exists()) {
        return myResult;
      }
      DataImportService myData = DataImportService.getInstance();
      String strXml = Utility.fileToString(datasetFile.getPath());
      boolean filterOrganizations = true;
      myResult = myData.importDataFromXML(client, organization, strXml, dataset.getModule(),
          filterOrganizations);

      if (myResult.getErrorMessages() != null && !myResult.getErrorMessages().equals("")
          && !myResult.getErrorMessages().equals("null")) {
        return myResult;
      }
      if (organization.getId().equals(getZeroOrg().getId())
          && getClientModuleList(client, dataset.getModule()).size() == 0) {
        insertClientModule(client, dataset.getModule());
      } else if (getOrgModuleList(client, organization, dataset.getModule()).size() == 0) {
        insertOrgModule(client, organization, dataset.getModule());
      }
    } finally {
      OBContext.restorePreviousMode();
    }

    return myResult;
  }

  private static List<ADClientModule> getClientModuleList(Client client, Module module) {
    OBContext.setAdminMode();
    try {
      OBCriteria<ADClientModule> clientModules = OBDal.getInstance().createCriteria(
          ADClientModule.class);
      clientModules.add(Restrictions.eq(ADClientModule.PROPERTY_CLIENT, client));
      clientModules.add(Restrictions.eq(ADClientModule.PROPERTY_MODULE, module));
      clientModules.setFilterOnReadableOrganization(false);
      clientModules.setFilterOnReadableClients(false);
      return clientModules.list();
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  private static List<ADOrgModule> getOrgModuleList(Client client, Organization organization,
      Module module) {
    OBContext.setAdminMode();
    try {
      OBCriteria<ADOrgModule> orgModules = OBDal.getInstance().createCriteria(ADOrgModule.class);
      orgModules.add(Restrictions.eq(ADOrgModule.PROPERTY_CLIENT, client));
      orgModules.add(Restrictions.eq(ADOrgModule.PROPERTY_ORGANIZATION, organization));
      orgModules.add(Restrictions.eq(ADOrgModule.PROPERTY_MODULE, module));
      orgModules.setFilterOnReadableOrganization(false);
      orgModules.setFilterOnReadableClients(false);
      return orgModules.list();
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * Returns the set of Module objects for the given ids
   * 
   * @param strModules
   *          relation of ids (in a format so that can be included in a "in" statement of a "where"
   *          clause
   * @return List<Module> with the relation of modules
   * @throws Exception
   */
  public static List<Module> getCOAModules(String strModules) throws Exception {
    StringBuilder strWhereClause = new StringBuilder();
    strWhereClause.append(" as module where module.id in (" + strModules + ")");
    strWhereClause.append(" and module.hasChartOfAccounts = 'Y'");
    final OBQuery<Module> obqModule = OBDal.getInstance().createQuery(Module.class,
        strWhereClause.toString());
    return obqModule.list();
  }

  /**
   * Returns the set of Module objects for the given ids
   * 
   * @param strModules
   *          relation of ids (in a format so that can be included in a "in" statement of a "where"
   *          clause
   * @throws Exception
   */
  public static List<Module> getRDModules(String strModules) throws Exception {
    StringBuilder strWhereClause = new StringBuilder();
    strWhereClause.append(" as module where module.id in (" + strModules + ")");
    strWhereClause.append(" and module.hasReferenceData = 'Y'");
    strWhereClause.append(" and module.hasChartOfAccounts = 'N'");
    final OBQuery<Module> obqModule = OBDal.getInstance().createQuery(Module.class,
        strWhereClause.toString());
    return obqModule.list();
  }

  /**
   * @deprecated use {@link #getDataSets(Module, List)}
   * 
   * @param module
   * @param accessLevel
   *          3-> client/org; 1-> organization only
   * @return List<DataSet> with the relation of DataSet objects
   * @throws Exception
   */
  @Deprecated
  public static List<DataSet> getDataSets(Module module, String accessLevel) throws Exception {
    ArrayList<String> coAccessLevel = new ArrayList<String>();
    coAccessLevel.add(accessLevel);
    return getDataSets(module, coAccessLevel);
  }

  /**
   * Given a module, and an access level, returns all the datasets contained in that module
   * 
   * @param module
   * @param accessLevel
   *          3-> client/org; 6-> System/client
   * @return List<DataSet> with the relation of DataSet objects
   * @throws Exception
   */
  public static List<DataSet> getDataSets(Module module, List<String> accessLevel) throws Exception {
    OBContext.setAdminMode();
    try {
      final OBCriteria<DataSet> obcDataSets = OBDal.getInstance().createCriteria(DataSet.class);
      obcDataSets.add(Restrictions.eq(DataSet.PROPERTY_MODULE, module));
      obcDataSets.add(Restrictions.in(DataSet.PROPERTY_DATAACCESSLEVEL, accessLevel));
      obcDataSets.addOrder(Order.asc(DataSet.PROPERTY_NAME));
      if (obcDataSets.list().size() > 0) {
        return obcDataSets.list();
      } else {
        return null;
      }
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * 
   * @param client
   * @param orgProvided
   *          optional parameter. If not provided, "*" organization used
   * @param module
   * @return ADClientModule object with the created element
   * @throws Exception
   */
  public static ADOrgModule insertOrgModule(Client client, Organization orgProvided, Module module)
      throws Exception {
    Organization org;
    if (orgProvided == null) {
      if ((org = getZeroOrg()) == null) {
        return null;
      }
    } else {
      org = orgProvided;
    }

    OBContext.setAdminMode();
    try {
      final ADOrgModule newADOrgModule = OBProvider.getInstance().get(ADOrgModule.class);
      newADOrgModule.setClient(client);
      newADOrgModule.setOrganization(org);
      newADOrgModule.setModule(module);
      newADOrgModule.setVersion(module.getVersion());
      newADOrgModule.setChecksum(getModuleDatasetsChechsum(module));

      OBDal.getInstance().save(newADOrgModule);
      OBDal.getInstance().flush();
      return newADOrgModule;
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  private static String getModuleDatasetsChechsum(Module module) {
    String checksum = "";
    OBContext.setAdminMode();
    try {
      OBCriteria<DataSet> obc = OBDal.getInstance().createCriteria(DataSet.class);
      obc.createAlias(DataSet.PROPERTY_MODULE, "m");
      obc.add(Restrictions.eq(DataSet.PROPERTY_MODULE, module));
      String[] organizationAccessLevel = { "3", "1" };
      String[] systemAccessLevel = { "3", "6" };
      obc.add(Restrictions.or(Restrictions.and(
          Restrictions.ne(DataSet.PROPERTY_ORGANIZATION, getZeroOrg()),
          Restrictions.in(DataSet.PROPERTY_DATAACCESSLEVEL, organizationAccessLevel)), Restrictions
          .and(Restrictions.eq(DataSet.PROPERTY_ORGANIZATION, getZeroOrg()),
              Restrictions.in(DataSet.PROPERTY_DATAACCESSLEVEL, systemAccessLevel))));
      obc.addOrder(Order.asc("m." + Module.PROPERTY_ID));
      obc.addOrder(Order.asc(DataSet.PROPERTY_SEQUENCENUMBER));
      obc.addOrder(Order.asc(DataSet.PROPERTY_ID));
      obc.setFilterOnReadableClients(false);
      obc.setFilterOnReadableOrganization(false);
      for (DataSet dataset : obc.list()) {
        if (checksum.length() > 0 && !StringUtils.isEmpty(dataset.getChecksum())) {
          checksum = checksum + ",";
        }
        checksum = checksum + (dataset.getChecksum() == null ? "" : dataset.getChecksum());
      }
    } finally {
      OBContext.restorePreviousMode();
    }
    return checksum;
  }

  /**
   * 
   * @param client
   * @param module
   * @return ADClientModule object with the created element
   * @throws Exception
   */
  public static ADClientModule insertClientModule(Client client, Module module) throws Exception {
    OBContext.setAdminMode();
    try {
      final ADClientModule newADClientModule = OBProvider.getInstance().get(ADClientModule.class);
      newADClientModule.setClient(client);
      newADClientModule.setOrganization(getZeroOrg());
      newADClientModule.setModule(module);
      newADClientModule.setVersion(module.getVersion());
      OBDal.getInstance().save(newADClientModule);
      OBDal.getInstance().flush();
      return newADClientModule;
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  public static boolean existsOrgName(Client client, String strOrgName) throws Exception {
    OBContext.setAdminMode();
    try {
      final OBCriteria<Organization> obcOrg = OBDal.getInstance()
          .createCriteria(Organization.class);
      obcOrg.setFilterOnReadableOrganization(false);
      obcOrg.add(Restrictions.eq(Organization.PROPERTY_CLIENT, client));
      obcOrg.add(Restrictions.eq(Organization.PROPERTY_NAME, strOrgName));
      return obcOrg.count() > 0;
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  public static Organization insertOrganization(String strOrgName, OrganizationType orgType,
      String strcLocationId, Client client) throws Exception {
    return insertOrganization(strOrgName, orgType, strcLocationId, client, null);

  }

  public static Organization insertOrganization(String strOrgName, OrganizationType orgType,
      String strcLocationId, Client client, Currency strCurrency) throws Exception {
    log4j.debug("InitialSetupUtility - insertOrganization() - name = " + strOrgName);
    OBContext.setAdminMode();
    try {
      final Organization newOrg = OBProvider.getInstance().get(Organization.class);
      newOrg.setClient(client);
      newOrg.setName(strOrgName);
      newOrg.setSearchKey(strOrgName);
      newOrg.setOrganizationType(orgType);
      newOrg.setCurrency(strCurrency);
      OBDal.getInstance().save(newOrg);
      OBDal.getInstance().flush();
      return newOrg;
    } finally {
      OBContext.restorePreviousMode();
    }

  }

  public static Tree getOrgTree(Client client) throws Exception {
    OBCriteria<Tree> obcTree = OBDal.getInstance().createCriteria(Tree.class);
    final String AD_ORG_TABLE_ID = "155";
    Table table = OBDal.getInstance().get(Table.class, AD_ORG_TABLE_ID);
    obcTree.add(Restrictions.eq(Tree.PROPERTY_TABLE, table));
    obcTree.add(Restrictions.eq(Tree.PROPERTY_CLIENT, client));
    return obcTree.list().get(0);
  }

  public static TreeNode getTreeNode(Organization org, Tree tree, Client client) throws Exception {
    OBContext.setAdminMode();
    try {
      final OBCriteria<TreeNode> obcTreeNode = OBDal.getInstance().createCriteria(TreeNode.class);
      obcTreeNode.setFilterOnReadableOrganization(false);
      obcTreeNode.add(Restrictions.eq(TreeNode.PROPERTY_TREE, tree));
      obcTreeNode.add(Restrictions.eq(TreeNode.PROPERTY_CLIENT, client));
      obcTreeNode.add(Restrictions.eq(TreeNode.PROPERTY_NODE, org.getId()));
      return obcTreeNode.list().get(0);
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  public static void updateOrgTree(Tree tree, TreeNode orgNode, Organization parentOrg)
      throws Exception {
    Long lSeqNo = 0L;
    orgNode.setReportSet(parentOrg.getId());
    OBContext.setAdminMode();
    try {
      final OBCriteria<TreeNode> obcTreeNodes = OBDal.getInstance().createCriteria(TreeNode.class);
      obcTreeNodes.setFilterOnReadableClients(false);
      obcTreeNodes.setFilterOnReadableOrganization(false);
      obcTreeNodes.add(Restrictions.eq(TreeNode.PROPERTY_REPORTSET, parentOrg.getId()));
      obcTreeNodes.add(Restrictions.eq(TreeNode.PROPERTY_TREE, tree));
      for (TreeNode treeNode : obcTreeNodes.list())
        if (treeNode.getSequenceNumber() > lSeqNo)
          lSeqNo = treeNode.getSequenceNumber();
      orgNode.setSequenceNumber(lSeqNo + 10L);
      OBDal.getInstance().save(orgNode);
      OBDal.getInstance().flush();
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  public static String getTranslatedMessage(Language language, String msgId) {
    OBContext.setAdminMode();
    try {
      Message msg = OBDal.getInstance().get(Message.class, msgId);
      OBCriteria<MessageTrl> obcMsgTrl = OBDal.getInstance().createCriteria(MessageTrl.class);
      obcMsgTrl.setFilterOnReadableClients(false);
      obcMsgTrl.setFilterOnReadableOrganization(false);
      obcMsgTrl.add(Restrictions.eq(MessageTrl.PROPERTY_MESSAGE, msg));
      obcMsgTrl.add(Restrictions.eq(MessageTrl.PROPERTY_LANGUAGE, language));
      MessageTrl trl = (MessageTrl) obcMsgTrl.uniqueResult();
      if (trl == null) {
        return msg.getMessageText();
      }
      return trl.getMessageText();
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  public static String getTranslatedColumnName(Language language, String columnName) {
    OBContext.setAdminMode();
    try {
      OBCriteria<org.openbravo.model.ad.ui.Element> obcElement = OBDal.getInstance()
          .createCriteria(org.openbravo.model.ad.ui.Element.class);
      obcElement.setFilterOnReadableClients(false);
      obcElement.setFilterOnReadableOrganization(false);
      obcElement.add(Restrictions.eq(org.openbravo.model.ad.ui.Element.PROPERTY_DBCOLUMNNAME,
          columnName));
      org.openbravo.model.ad.ui.Element element = (org.openbravo.model.ad.ui.Element) obcElement
          .uniqueResult();

      OBCriteria<ElementTrl> obcElementTrl = OBDal.getInstance().createCriteria(ElementTrl.class);
      obcElementTrl.add(Restrictions.eq(ElementTrl.PROPERTY_APPLICATIONELEMENT, element));
      obcElementTrl.add(Restrictions.eq(ElementTrl.PROPERTY_LANGUAGE, language));
      ElementTrl trl = (ElementTrl) obcElementTrl.uniqueResult();
      if (trl == null) {
        return element.getName();
      }
      return trl.getName();
    } catch (final Exception err) {
      return "Error!";
    } finally {
      OBContext.restorePreviousMode();
    }
  }

  /**
   * Returns the tree of the provided type
   * 
   * @param strTreeTypeMenu
   *          two letters corresponding to the tree type for the menu
   * @return Tree menu element (defined at system level)
   * @throws Exception
   */
  public static Tree getTree(String strTreeTypeMenu, Client client, Organization orgProvided)
      throws Exception {
    Organization organization;
    if (orgProvided == null) {
      if ((organization = getZeroOrg()) == null)
        return null;
    } else
      organization = orgProvided;

    final OBCriteria<Tree> obcTree = OBDal.getInstance().createCriteria(Tree.class);
    obcTree.add(Restrictions.eq(Tree.PROPERTY_TYPEAREA, strTreeTypeMenu));
    obcTree.add(Restrictions.eq(Tree.PROPERTY_CLIENT, client));
    obcTree.add(Restrictions.eq(Tree.PROPERTY_ORGANIZATION, organization));
    List<Tree> lTrees = obcTree.list();
    if (lTrees.size() != 1)
      return null;
    return lTrees.get(0);
  }

  /**
   * Returns the ADTree associated with the given table
   * 
   * @return Tree menu element (defined at system level)
   * @throws Exception
   */
  public static Tree getTree(Table table, Client client, Organization orgProvided) throws Exception {
    Organization organization;
    if (orgProvided == null) {
      if ((organization = getZeroOrg()) == null)
        return null;
    } else
      organization = orgProvided;

    final OBCriteria<Tree> obcTree = OBDal.getInstance().createCriteria(Tree.class);
    obcTree.add(Restrictions.eq(Tree.PROPERTY_TABLE, table));
    obcTree.add(Restrictions.eq(Tree.PROPERTY_CLIENT, client));
    obcTree.add(Restrictions.eq(Tree.PROPERTY_ORGANIZATION, organization));
    List<Tree> lTrees = obcTree.list();
    if (lTrees.size() != 1)
      return null;
    return lTrees.get(0);
  }

}
